import pandas as pd
import re
from datetime import datetime
import numpy as np
import os

def process_id_card_string(id_card):
    """处理身份证号码字符串，去除可能的空格和转换为字符串类型"""
    if pd.isna(id_card):
        return ""
    # 转换为字符串并去除空格
    id_card = str(id_card).strip()
    # 如果是科学计数法表示的数字，尝试转换
    if 'e' in id_card.lower() or 'E' in id_card:
        try:
            # 转换为浮点数再转为整数，最后转为字符串
            id_card = str(int(float(id_card)))
        except:
            pass
    # 去除可能的前导零（如果长度超过18位且前面有很多零）
    # 但保留正常的18位身份证格式
    if len(id_card) > 18 and id_card.startswith('0'):
        # 尝试找到有效数字开始的位置
        match = re.search(r'[1-9]', id_card)
        if match:
            id_card = id_card[match.start():]
    return id_card

def calculate_check_code(id_card_17):
    """根据17位身份证号码计算校验码"""
    # 权重系数
    weights = [7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2]
    # 校验码映射
    check_codes = ['1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2']
    
    # 计算加权和
    total = sum(int(id_card_17[i]) * weights[i] for i in range(17))
    # 取模得到校验码索引
    check_index = total % 11
    # 返回校验码
    return check_codes[check_index]

def validate_id_card(id_card):
    """
    验证身份证号码的有效性
    返回: (是否有效, 错误信息, 修复建议, 是否为空)
    """
    # 预处理身份证号码
    id_card = process_id_card_string(id_card)
    
    # 检查是否为空
    if not id_card:
        return False, "身份证号码为空", "", True
    
    # 检查长度
    if len(id_card) != 18:
        # 如果是17位，尝试修复
        if len(id_card) == 17:
            # 检查是否都是数字
            if re.match(r'^[0-9]{17}$', id_card):
                # 计算校验码
                check_code = calculate_check_code(id_card)
                fixed_id = id_card + check_code
                return False, "身份证号码长度错误（缺少校验码）", f"建议修复为: {fixed_id}", False
        # 其他长度错误
        return False, f"身份证号码长度错误（应为18位，实际为{len(id_card)}位）", "", False
    
    # 检查格式（前17位为数字，最后一位为数字或X）
    if not re.match(r'^[0-9]{17}[0-9Xx]$', id_card):
        return False, "身份证号码格式错误（前17位必须为数字，最后一位为数字或X）", "", False
    
    # 检查出生日期
    try:
        birth_year = int(id_card[6:10])
        birth_month = int(id_card[10:12])
        birth_day = int(id_card[12:14])
        
        # 检查月份范围
        if birth_month < 1 or birth_month > 12:
            return False, f"身份证号码中的出生日期无效（月份{birth_month}超出范围）", "", False
        
        # 检查日期范围
        days_in_month = [0, 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]
        # 检查闰年
        if birth_month == 2:
            if (birth_year % 4 == 0 and birth_year % 100 != 0) or (birth_year % 400 == 0):
                days_in_month[2] = 29
        
        if birth_day < 1 or birth_day > days_in_month[birth_month]:
            return False, f"身份证号码中的出生日期无效（日期{birth_day}超出范围）", "", False
        
        # 检查年份合理性（假设年龄在0-150岁之间）
        current_year = datetime.now().year
        if birth_year < current_year - 150 or birth_year > current_year:
            return False, f"身份证号码中的出生年份不合理（{birth_year}）", "", False
            
    except Exception as e:
        return False, f"身份证号码中的出生日期格式错误: {str(e)}", "", False
    
    # 检查校验码
    id_card_17 = id_card[:17]
    expected_check_code = calculate_check_code(id_card_17)
    actual_check_code = id_card[17].upper()
    
    if actual_check_code != expected_check_code:
        # 提供修复建议
        fixed_id = id_card_17 + expected_check_code
        return False, "身份证号码校验码错误", f"建议修复为: {fixed_id}", False
    
    # 所有验证都通过
    return True, "", "", False

def find_id_card_columns(df):
    """自动识别可能包含身份证号码的列"""
    id_card_columns = []
    
    # 基于列名的初步判断
    id_keywords = ['身份证', '身份证号', '身份证号码', '证件号码', '证件号']
    
    for col in df.columns:
        # 检查列名是否包含关键字
        if any(keyword in col for keyword in id_keywords):
            id_card_columns.append(col)
        else:
            # 采样检查该列的数据是否可能包含身份证号码
            sample_data = df[col].dropna().head(10)
            id_card_count = 0
            
            for val in sample_data:
                processed_val = process_id_card_string(val)
                # 如果长度接近18位或格式类似身份证
                if (len(processed_val) in [17, 18] and 
                    re.match(r'^[0-9]{17}[0-9Xx]?$', processed_val)):
                    id_card_count += 1
            
            # 如果超过一半的样本看起来像身份证号码，则认为这是身份证列
            if len(sample_data) > 0 and id_card_count / len(sample_data) > 0.5:
                id_card_columns.append(col)
    
    return id_card_columns

def generate_html_report(df, id_card_columns, total_valid, total_invalid, all_invalid_ids):
    """生成HTML格式的验证报告"""
    
    # 创建HTML头部
    html = f'''
    <!DOCTYPE html>
    <html lang="zh-CN">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>身份证号码验证报告</title>
        <style>
            body {{
                font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, 'Helvetica Neue', Arial, sans-serif;
                line-height: 1.6;
                color: #333;
                max-width: 1400px;
                margin: 0 auto;
                padding: 20px;
                background-color: #f5f5f5;
            }}
            h1, h2, h3 {{
                color: #2c3e50;
            }}
            .summary {{
                background-color: #fff;
                padding: 25px;
                border-radius: 8px;
                box-shadow: 0 2px 4px rgba(0,0,0,0.1);
                margin-bottom: 30px;
            }}
            .stats {{
                display: grid;
                grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
                gap: 20px;
                margin-top: 20px;
            }}
            .stat-card {{
                background-color: #ecf0f1;
                padding: 20px;
                border-radius: 8px;
                text-align: center;
            }}
            .stat-number {{
                font-size: 2.5em;
                font-weight: bold;
                color: #3498db;
            }}
            .stat-label {{
                font-size: 1.1em;
                color: #7f8c8d;
            }}
            .data-table {{
                background-color: #fff;
                padding: 25px;
                border-radius: 8px;
                box-shadow: 0 2px 4px rgba(0,0,0,0.1);
                overflow-x: auto;
                margin-bottom: 30px;
            }}
            table {{
                width: 100%;
                border-collapse: collapse;
                margin-top: 20px;
            }}
            th, td {{
                padding: 12px;
                text-align: left;
                border-bottom: 1px solid #ddd;
            }}
            th {{
                background-color: #34495e;
                color: white;
                font-weight: 600;
                position: sticky;
                top: 0;
            }}
            tr:hover {{
                background-color: #f5f5f5;
            }}
            .valid {{ color: #27ae60; }}
            .invalid {{ color: #e74c3c; }}
            .highlight-invalid {{ 
                background-color: #fdedec !important;
                font-weight: bold;
            }}
            .empty {{ color: #95a5a6; }}
            .highlight-empty {{ 
                background-color: #f8f9fa !important;
                font-weight: bold;
            }}
            .suggestion {{ color: #f39c12; }}
            .error-summary {{
                background-color: #fff;
                padding: 25px;
                border-radius: 8px;
                box-shadow: 0 2px 4px rgba(0,0,0,0.1);
            }}
            .error-list {{ 
                list-style-type: none;
                padding: 0;
            }}
            .error-item {{
                padding: 10px;
                margin-bottom: 10px;
                background-color: #fdedec;
                border-left: 4px solid #e74c3c;
                border-radius: 4px;
            }}
            .suggestion-box {{
                background-color: #fff3cd;
                border: 1px solid #ffeaa7;
                border-radius: 4px;
                padding: 8px;
                margin-top: 5px;
            }}
            /* 列选择器样式 */
            .column-selector {{
                margin-bottom: 20px;
                padding: 15px;
                background-color: #f8f9fa;
                border-radius: 6px;
                border: 1px solid #dee2e6;
            }}
            .column-selector h3 {{
                margin-top: 0;
                margin-bottom: 15px;
                color: #495057;
            }}
            .column-controls {{
                display: flex;
                gap: 10px;
                margin-bottom: 15px;
                align-items: center;
            }}
            .btn {{
                padding: 8px 16px;
                border: none;
                border-radius: 4px;
                cursor: pointer;
                font-size: 14px;
                transition: background-color 0.3s;
            }}
            .btn-primary {{
                background-color: #3498db;
                color: white;
            }}
            .btn-primary:hover {{
                background-color: #2980b9;
            }}
            .btn-secondary {{
                background-color: #6c757d;
                color: white;
            }}
            .btn-secondary:hover {{
                background-color: #545b62;
            }}
            .btn-success {{
                background-color: #27ae60;
                color: white;
            }}
            .btn-success:hover {{
                background-color: #219a52;
            }}
            .column-groups {{
                display: grid;
                grid-template-columns: repeat(auto-fit, minmax(250px, 1fr));
                gap: 20px;
            }}
            .column-group {{
                background-color: #ffffff;
                padding: 15px;
                border-radius: 6px;
                border: 1px solid #e9ecef;
                max-height: 300px;
                overflow-y: auto;
            }}
            .column-group h4 {{
                margin-top: 0;
                margin-bottom: 10px;
                color: #495057;
                font-size: 14px;
                font-weight: 600;
            }}
            .column-item {{
                margin-bottom: 8px;
                display: flex;
                align-items: center;
                font-size: 14px;
            }}
            .column-item input[type="checkbox"] {{
                margin-right: 8px;
            }}
            .hidden {{
                display: none;
            }}
        </style>
        <script>
            // 保存列显示状态到localStorage
            function saveColumnState() {{
                const columnStates = {{
                }};
                document.querySelectorAll('.column-selector input[type="checkbox"]').forEach(checkbox => {{
                    columnStates[checkbox.value] = checkbox.checked;
                }});
                localStorage.setItem('idCardReportColumns', JSON.stringify(columnStates));
            }}
            
            // 从localStorage加载列显示状态
            function loadColumnState() {{
                const savedStates = localStorage.getItem('idCardReportColumns');
                if (savedStates) {{
                    const columnStates = JSON.parse(savedStates);
                    document.querySelectorAll('.column-selector input[type="checkbox"]').forEach(checkbox => {{
                        if (columnStates.hasOwnProperty(checkbox.value)) {{
                            checkbox.checked = columnStates[checkbox.value];
                            toggleColumn(checkbox);
                        }}
                    }});
                }}
            }}
            
            // 切换列的显示/隐藏
            function toggleColumn(checkbox) {{
                const columnIndex = parseInt(checkbox.dataset.index);
                const table = document.querySelector('.data-table table');
                
                // 切换表头列
                const headerCells = table.querySelectorAll('thead th');
                if (headerCells[columnIndex]) {{
                    headerCells[columnIndex].classList.toggle('hidden', !checkbox.checked);
                }}
                
                // 切换数据行中的列
                const dataRows = table.querySelectorAll('tbody tr');
                dataRows.forEach(row => {{
                    const cells = row.querySelectorAll('td');
                    if (cells[columnIndex]) {{
                        cells[columnIndex].classList.toggle('hidden', !checkbox.checked);
                    }}
                }});
                
                // 保存状态
                saveColumnState();
            }}
            
            // 全选/取消全选
            function selectAll(selectAllCheckbox) {{
                const isChecked = selectAllCheckbox.checked;
                document.querySelectorAll('.column-selector input[type="checkbox"].column-checkbox').forEach(checkbox => {{
                    checkbox.checked = isChecked;
                    toggleColumn(checkbox);
                }});
                saveColumnState();
            }}
            
            // 仅显示验证相关列
            function showOnlyValidationColumns() {{
                document.querySelectorAll('.column-selector input[type="checkbox"].column-checkbox').forEach(checkbox => {{
                    const isValidationColumn = checkbox.value.includes('验证') || checkbox.value.includes('修复');
                    checkbox.checked = isValidationColumn;
                    toggleColumn(checkbox);
                }});
                saveColumnState();
            }}
            
            // 重置为默认显示
            function resetToDefault() {{
                document.querySelectorAll('.column-selector input[type="checkbox"].column-checkbox').forEach(checkbox => {{
                    // 默认显示所有列
                    checkbox.checked = true;
                    toggleColumn(checkbox);
                }});
                document.querySelector('#select-all').checked = true;
                localStorage.removeItem('idCardReportColumns');
            }}
            
            // 页面加载完成后初始化
            document.addEventListener('DOMContentLoaded', function() {{
                loadColumnState();
                
                // 为所有复选框添加事件监听器
                document.querySelectorAll('.column-selector input[type="checkbox"].column-checkbox').forEach(checkbox => {{
                    checkbox.addEventListener('change', function() {{
                        toggleColumn(this);
                    }});
                }});
                
                // 为全选按钮添加事件监听器
                document.querySelector('#select-all').addEventListener('change', function() {{
                    selectAll(this);
                }});
                
                // 为仅显示验证列按钮添加事件监听器
                document.querySelector('#show-validation').addEventListener('click', showOnlyValidationColumns);
                
                // 为重置按钮添加事件监听器
                document.querySelector('#reset-columns').addEventListener('click', resetToDefault);
            }});
        </script>
    </head>
    <body>
        <h1>身份证号码验证报告</h1>
        <div class="summary">
            <h2>验证摘要</h2>
            <p>生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}</p>
            <p>数据行数: {len(df)}</p>
            <p>识别到的身份证列: {', '.join(id_card_columns)}</p>
            
            <div class="stats">
                <div class="stat-card">
                    <div class="stat-number">{total_valid}</div>
                    <div class="stat-label">有效号码</div>
                </div>
                <div class="stat-card">
                    <div class="stat-number">{total_invalid}</div>
                    <div class="stat-label">无效号码</div>
                </div>
                <div class="stat-card">
                    <div class="stat-number">{total_valid + total_invalid}</div>
                    <div class="stat-label">总计检查</div>
                </div>
                <div class="stat-card">
                    <div class="stat-number">{total_valid/(total_valid+total_invalid)*100:.2f}%</div>
                    <div class="stat-label">有效率</div>
                </div>
            </div>
        </div>
    '''
    
    # 添加错误汇总
    if all_invalid_ids:
        html += '''
        <div class="error-summary">
            <h2>错误汇总</h2>
            <ul class="error-list">
        '''
        
        # 按错误类型分组
        error_groups = {}
        for col, invalid_ids in all_invalid_ids.items():
            for idx, id_info in invalid_ids.items():
                error_msg = id_info.get('error_msg', '未知错误')
                if error_msg not in error_groups:
                    error_groups[error_msg] = []
                error_groups[error_msg].append({
                    'column': col,
                    'row': idx,
                    'id_card': id_info.get('id_card', ''),
                    'suggestion': id_info.get('suggestion', '')
                })
        
        # 输出错误分组
        for error_msg, instances in error_groups.items():
            html += f'''
                <li class="error-item">
                    <strong>错误类型:</strong> {error_msg} <br>
                    <strong>影响数量:</strong> {len(instances)} <br>
            '''
            
            # 如果有修复建议，显示第一个作为示例
            has_suggestions = any(instance['suggestion'] for instance in instances)
            if has_suggestions:
                for instance in instances:
                    if instance['suggestion']:
                        html += f'''
                    <div class="suggestion-box">
                        <strong>示例修复建议:</strong> {instance['suggestion']} (行: {instance['row']})
                    </div>
                        '''
                        break
            
            html += '''
                </li>
            '''
        
        html += '''
            </ul>
        </div>
        '''
    
    # 添加数据表格 - 包含列选择器
    html += '''
        <div class="data-table">
            <h2>详细数据</h2>
            
            <!-- 列选择器 -->
            <div class="column-selector">
                <h3>列显示设置</h3>
                <div class="column-controls">
                    <label class="column-item">
                        <input type="checkbox" id="select-all" checked>
                        全选
                    </label>
                    <button class="btn btn-primary" id="show-validation">仅显示验证列</button>
                    <button class="btn btn-secondary" id="reset-columns">重置显示</button>
                </div>
                
                <div class="column-groups">
                    <div class="column-group">
                        <h4>基础信息列</h4>
    '''
    
    # 添加列选择器的复选框 - 将列分组显示
    basic_columns = []
    validation_columns = []
    
    for i, col in enumerate(df.columns):
        if "验证状态" in col or "修复建议" in col:
            validation_columns.append((i, col))
        else:
            basic_columns.append((i, col))
    
    # 添加基础信息列的复选框
    for i, col in basic_columns:
        html += f'''
                        <label class="column-item">
                            <input type="checkbox" class="column-checkbox" value="{col}" data-index="{i}" checked>
                            {col}
                        </label>
        '''
    
    html += '''
                    </div>
                    <div class="column-group">
                        <h4>验证相关列</h4>
    '''
    
    # 添加验证相关列的复选框
    for i, col in validation_columns:
        html += f'''
                        <label class="column-item">
                            <input type="checkbox" class="column-checkbox" value="{col}" data-index="{i}" checked>
                            {col}
                        </label>
        '''
    
    html += '''
                    </div>
                </div>
            </div>
            
            <!-- 数据表 -->
            <table>
                <thead>
                    <tr>
    '''
    
    # 添加表头
    for col in df.columns:
        html += f'''<th>{col}</th>'''
    
    html += '''
                    </tr>
                </thead>
                <tbody>
    '''
    
    # 添加数据行
    for idx, row in df.iterrows():
        # 检查是否有错误或未填写的身份证号码
        has_invalid_id = False
        has_empty_id = False
        for col in id_card_columns:
            status_col = f"{col}_验证状态"
            if status_col in row:
                if row[status_col] == "错误":
                    has_invalid_id = True
                elif row[status_col] == "未填写":
                    has_empty_id = True
        
        # 添加行，高亮错误或未填写行
        if has_invalid_id:
            row_class = "highlight-invalid"
        elif has_empty_id:
            row_class = "highlight-empty"
        else:
            row_class = ""
        html += f'''
                    <tr class="{row_class}">
        '''
        
        # 添加单元格
        for col in df.columns:
            cell_value = row[col]
            
            # 特殊处理验证状态列
            if "验证状态" in col and cell_value == "有效":
                html += f'''<td class="valid">{cell_value}</td>'''
            elif "验证状态" in col and cell_value == "错误":
                html += f'''<td class="invalid">{cell_value}</td>'''
            elif "验证状态" in col and cell_value == "未填写":
                html += f'''<td class="empty">{cell_value}</td>'''
            # 特殊处理修复建议列
            elif "修复建议" in col and pd.notna(cell_value) and cell_value:
                html += f'''<td class="suggestion">{cell_value}</td>'''
            else:
                # 处理可能的NaN值
                if pd.isna(cell_value):
                    cell_value = ""
                html += f'''<td>{cell_value}</td>'''
        
        html += '''
                    </tr>
        '''
    
    html += '''
                </tbody>
            </table>
        </div>
    </body>
    </html>
    '''
    
    return html

def main():
    # 读取Excel文件
    file_path = 'data.xlsx'
    print(f"正在读取文件: {file_path}")
    
    try:
        # 使用dtype=str避免自动将数字转换为科学计数法
        df = pd.read_excel(file_path, dtype=str)
        print(f"文件读取成功！共 {len(df)} 行，{len(df.columns)} 列")
        
        # 自动识别可能包含身份证号码的列
        id_card_columns = find_id_card_columns(df)
        print(f"识别到 {len(id_card_columns)} 个可能包含身份证号码的列: {', '.join(id_card_columns)}")
        
        # 统计信息
        total_valid = 0
        total_invalid = 0
        all_invalid_ids = {}  # 存储所有无效的身份证信息
        
        # 对每个可能的身份证列进行验证
        for col in id_card_columns:
            print(f"\n验证列: {col}")
            valid_count = 0
            invalid_count = 0
            invalid_ids = {}  # 存储当前列无效的身份证信息
            
            # 为每个身份证列添加验证状态和修复建议列
            status_col = f"{col}_验证状态"
            suggestion_col = f"{col}_修复建议"
            df[status_col] = ""
            df[suggestion_col] = ""
            
            # 验证该列中的每个值
            for idx, id_card in df[col].items():
                is_valid, error_msg, suggestion, is_empty = validate_id_card(id_card)
                
                # 更新状态和建议列
                if is_valid:
                    df.at[idx, status_col] = "有效"
                elif is_empty:
                    df.at[idx, status_col] = "未填写"
                else:
                    df.at[idx, status_col] = "错误"
                
                if suggestion:
                    df.at[idx, suggestion_col] = suggestion
                
                # 更新统计
                if is_valid:
                    valid_count += 1
                else:
                    invalid_count += 1
                    if idx not in invalid_ids:
                        invalid_ids[idx] = {}
                    invalid_ids[idx]['id_card'] = id_card
                    invalid_ids[idx]['error_msg'] = error_msg
                    invalid_ids[idx]['suggestion'] = suggestion
                    invalid_ids[idx]['is_empty'] = is_empty
            
            # 更新总统计
            total_valid += valid_count
            total_invalid += invalid_count
            if invalid_ids:
                all_invalid_ids[col] = invalid_ids
            
            print(f"  有效: {valid_count}, 无效: {invalid_count}")
        
        # 计算总体有效率
        overall_validity_rate = (total_valid / (total_valid + total_invalid)) * 100 if (total_valid + total_invalid) > 0 else 0
        print(f"\n验证完成！总体有效率: {overall_validity_rate:.2f}%")
        print(f"总计: 有效 {total_valid} 个, 无效 {total_invalid} 个")
        
        # 生成HTML报告
        html_report = generate_html_report(df, id_card_columns, total_valid, total_invalid, all_invalid_ids)
        report_file = "身份证验证报告.html"
        with open(report_file, 'w', encoding='utf-8') as f:
            f.write(html_report)
        print(f"\nHTML报告已生成: {os.path.abspath(report_file)}")
        
    except Exception as e:
        print(f"处理文件时出错: {str(e)}")
        import traceback
        traceback.print_exc()

if __name__ == "__main__":
    main()